IST707 HW1: Data Preparation and Association Rule Mining¶
Part 0: Getting Started¶
Load packages and data¶
In [1]:
# import necessary libraries
import pandas as pd
from pandas import DataFrame, Series
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt
%matplotlib inline
import apyori as ap
from apyori import apriori #Apriori Algorithm
import mlxtend as ml
from mlxtend.frequent_patterns import apriori
from mlxtend.frequent_patterns import association_rules
from mlxtend.preprocessing import TransactionEncoder
import arules
# data formating
pd.set_option('display.max_columns',None)
pd.set_option('display.max_rows',None)
pd.set_option('max_colwidth',100)
In [2]:
# read data into a pandas dataframe
df = pd.read_csv("employee_attrition.csv",sep=',')
Reading and Displaying Data¶
- Number of Rows and Columns: 1176 rows and 35 columns
- Column Labels: Each column label is unique
- Empty Column: All columns are non-null
- Type of Values: float64(7), int64(19), object(9)
In [3]:
df.head(3)
Out[3]:
In [4]:
df.info()
Part 1: Data Understanding and Data Preparation¶
- Exploratory data analysis (EDA): derive descriptive statistics and apply data visualization to check for interesting data patterns
1.1 Derive Descriptive Statistics¶
Statistical summary of columns interpreted as numbers¶
- From the count column, we can see that there are missing values
- From the std column, we find two columns with no variations
In [5]:
df.describe().T
Out[5]:
Statistical summary for object type columns¶
- Over18 column has only 1 unique value. This column should be deleted.
In [6]:
col_obj=[x for x in df.columns.to_list() if str(df[x].dtype) == 'object']
df[col_obj].describe().T
Out[6]:
In [7]:
for col in col_obj:
print(df[col].value_counts())
print()
1.2 Apply Data Visualization to Check for Data Patterns¶
Correlation among numerical features (Heatmap)¶
- The correlation chart indicates that attributes such as age, job satisfaction, JobInvolvement, and stock optional level are postively correlated.
In [8]:
corr = df[['Age', 'DailyRate', 'DistanceFromHome', 'Education', 'EmployeeCount',
'EmployeeNumber', 'EnvironmentSatisfaction', 'HourlyRate',
'JobInvolvement', 'JobLevel', 'JobSatisfaction', 'MonthlyIncome',
'MonthlyRate', 'NumCompaniesWorked', 'PercentSalaryHike',
'PerformanceRating', 'RelationshipSatisfaction', 'StandardHours',
'StockOptionLevel', 'TotalWorkingYears', 'TrainingTimesLastYear',
'WorkLifeBalance', 'YearsAtCompany', 'YearsInCurrentRole',
'YearsSinceLastPromotion', 'YearsWithCurrManager']].corr()
sns.heatmap(corr, cmap="YlGnBu")
Out[8]:
Distribution of Each Categorical Variable (Pie Chart)¶
In [9]:
import plotly.express as px
for col in col_obj:
figure = px.pie(df,df[col],title = str(col))
figure.show()
Part 2: Data Preprocessing, Cleaning and Transformation¶
- Data preprocessing, cleaning, transformation: identify potential data quality issues and properly address those issues
2.1 Formating Categorical Attributes¶
In [10]:
# stripping columns
df_obj = df.select_dtypes(['object'])
df[df_obj.columns] = df_obj.apply(lambda x: x.str.strip())
2.2 Duplicates¶
In [11]:
if df.duplicated().sum()==0:
print('No duplicated rows')
2.3 Missing values¶
- Percentage of rows with missing values: around 0.9%
- Since we have very few number of rows that have missing vlues, we can delete the rows with missing values without impacting our Association Rule Mining Algorithm.
- In cases when the percentage of missing values is large, we can replace missing value with majority value for categorical data and we can replace missing value with median/mean for numeric data.
In [12]:
df.isnull().sum()
Out[12]:
In [13]:
df2 = df.dropna(axis = 0, how = 'any')
print("Number of rows with missing values deleted:", df.shape[0]-df2.shape[0])
2.4 Features with No Variances/Variations¶
Delete two features with no variances becuase they don't add value to the analysis.
- Column 'EmployeeCount'
- Column 'StandardHours'
- Column 'Over18'
In [14]:
df2['EmployeeCount'].value_counts()
del df2['EmployeeCount']
In [15]:
df2['StandardHours'].value_counts()
del df2['StandardHours']
In [16]:
df2['Over18'].value_counts()
del df2['Over18']
In [17]:
print("Number of columns deleted:", df.shape[1]-df2.shape[1])
print("Number of columns of df2:", df2.shape[1])
2.5 Features with Outliers¶
Each column has one extremely large value that needs to be deleted.
- 'TotalWorkingYears'
- 'YearsWithCurrManager'
- 'DistanceFromHome'
In [18]:
print('Maximum value of Total Working Years:', max(df2['TotalWorkingYears']))
print('Maximum value of Years With Manager:', max(df2['YearsWithCurrManager']))
print('Maximum value of Years With Manager:', max(df2['DistanceFromHome']))
In [19]:
df3 = df2.loc[
(df2['TotalWorkingYears']!=max(df2['TotalWorkingYears']))
& (df2["YearsWithCurrManager"]!=max(df2["YearsWithCurrManager"]))
&(df2['DistanceFromHome'] !=max(df2["DistanceFromHome"]))]
print('Number of columns with outliers deleted:', df2.shape[0]-df3.shape[0])
In [20]:
fig=plt.figure(figsize=(12,4)) # crate a new figure
ax0=fig.add_subplot(121)
ax0 = sns.boxplot(x=df2["TotalWorkingYears"])
ax0.set_title("With an extreme value",fontsize=10)
ax1=fig.add_subplot(122)
ax1 = sns.boxplot(x=df3["TotalWorkingYears"])
ax1.set_title("Without an extreme value",fontsize=10);
In [21]:
fig=plt.figure(figsize=(12,4)) # crate a new figure
ax0=fig.add_subplot(121)
ax0 = sns.boxplot(x=df2["YearsWithCurrManager"])
ax0.set_title("With an extreme value",fontsize=10)
ax1=fig.add_subplot(122)
ax1 = sns.boxplot(x=df3["YearsWithCurrManager"])
ax1.set_title("Without an extreme value",fontsize=10);
In [22]:
fig=plt.figure(figsize=(12,4)) # crate a new figure
ax0=fig.add_subplot(121)
ax0 = sns.boxplot(x=df2["DistanceFromHome"])
ax0.set_title("With an extreme value",fontsize=10)
ax1=fig.add_subplot(122)
ax1 = sns.boxplot(x=df3["DistanceFromHome"])
ax1.set_title("Without an extreme value",fontsize=10);
2.6 Discretize Numerical Attributes¶
2.6.1 Number of Numeric Columns¶
In [23]:
numeric_cols = df3.select_dtypes([np.number]).columns
print("Number of numeric columns",'\n',len(numeric_cols))
2.6.2 Distribution of Numeric Variables¶
In [24]:
df[numeric_cols].hist(figsize=(15,20));
2.6.3 Based on the above distribution plot, we can categorize numeric columns into 2 Types.¶
- Type 1: Columns that have relatively few unique values¶
- Type 2: Columns that have a relatively wide range of values
2.6.4 Since Type 1 columns has limited values. We simply change their dtypes from numeric to objects.¶
- Education: 5 levels(1,2,3,4,5)
- JobInvolvement: 4 levels(1,2,3,4)
- JobLevel: 5 levels(1,2,3,4,5)
- PerformanceRating: 2 levels(3,4)
- RelationshipSatisfaction: 4 levels(1,2,3,4)
- WorkLifeBalance: 4 levels (1,2,3,4)
- StockOptionLevel: 4 levels (0,1,2,3)
- JobSatisfaction: 4 levels (1,2,3,4)
- EnvironmentSatisfaction: 4 levels (1,2,3,4)
- TrainingTimesLastYear: 7 levels (0,1,2,3,4,5,6)
In [25]:
col_list = ['Education','JobInvolvement','JobLevel','PerformanceRating','RelationshipSatisfaction','WorkLifeBalance','StockOptionLevel','JobSatisfaction','EnvironmentSatisfaction','TrainingTimesLastYear']
for col in col_list:
print(df3[col].value_counts())
In [26]:
for col in col_list:
df3.astype({col: 'int32'}).dtypes
print(col,':',type(col))
In [27]:
print("Number of numeric columns transformed to objects",'\n',len(col_list))
2.6.5 Type2 Columns that have a relatively wide range of values. We process these columns using pd.qcut and pd.cut methods.¶
Use pd.qcut for
- MonthlyIncome
- YearsWithCurrManager
In [28]:
# pd.qcut
# MontlyIncome
df3['MonthlyIncome_grp'] = pd.qcut(df3['MonthlyIncome'],3,labels = ['low_mon_inc','med_mon_inc','high_mon_inc'])
# YearsWithCurrManager
df3['YearsWithCurrManager_grp'] = pd.qcut(df3['YearsWithCurrManager'],3,labels = ['rec_manager','some_time_manager','long_time_manager'])
Use pd.cut for
- Age
- DailyRate
- DistanceFromHome
- EmployeeNumber
- HourlyRate
- PercentSalaryHike
- TotalWorkingYears
- YearsAtCompany
- YearsInCurrentRole
- YearsSinceLastPromotion
- NumCompaniesWorked
In [29]:
# pd.cut
# NumCompaniesWorked
df3["NumCompaniesWorked_grp"] = pd.cut(df3["NumCompaniesWorked"], 3, labels =['few_comps','mid_comps','many_comps'])
# Age
df3['Age_grp'] = pd.cut(df3['Age'],4,labels = ['young_age','middle_age','senior_age','old_age'])
# DailyRate
df3['DailyRate_grp'] = pd.cut(df3['DailyRate'],3,labels = ['low_daily_rate','med_daily_rate','high_daily_rate'])
# DistanceFromHome
df3['DistanceFromHome_grp'] = pd.cut(df3['DistanceFromHome'],4,labels = ['walking_dist','short_dist','mid_dist','long_dist'])
# EmployeeNumber
df3['EmployeeNumber_grp'] = pd.cut(df3['EmployeeNumber'],3,labels = ['few_employ','many_employ','a_lot_employ'])
# HourlyRate
df3['HourlyRate_grp'] = pd.cut(df3['HourlyRate'],3,labels = ['low_hr_rate','med_hr_rate','high_hr_rate'])
# PercentSalaryHike
df3['PercentSalaryHike_grp'] = pd.cut(df3['PercentSalaryHike'],3,labels = ['low_per_hike','med_per_hike','high_per_hike'])
# TotalWorkingYears
df3['TotalWorkingYears_grp'] = pd.cut(df3['TotalWorkingYears'],3,labels = ['few_wrok_yrs','many_work_yrs','a_lot_work_yrs'])
# YearsAtCompany
df3['YearsAtCompany_grp'] = pd.cut(df3['YearsAtCompany'],3,labels = ['few_yrs_comp','many_yrs_comp','a_lot_yrs_comp'])
# YearsInCurrentRole
df3['YearsInCurrentRole_grp'] = pd.cut(df3['YearsInCurrentRole'],3,labels = ['few_yrs_role','many_yrs_role','a_lot_yrs_role'])
# YearsSinceLastPromotion
df3['YearsSinceLastPromotion_grp'] = pd.cut(df3['YearsSinceLastPromotion'],3,labels = ['rec_since_promo','some_time_since_promo','long_time_since_promo'])
# MontlyRate
df3['MonthlyRate_grp'] = pd.cut(df3['MonthlyRate'],3,labels = ['low_mon_rate','med_mon_rate','high_mon_rate'])
2.6.6 Drop the Categorical Columns¶
In [30]:
# list of columns to drop
col
col_to_drop = list(set(numeric_cols) - set(col_list))
print(col_to_drop)
In [31]:
# delete columns
for col in col_to_drop:
del df3[col]
In [32]:
print(df3.shape)
df3.head()
Out[32]:
2.7 Format Dataframe for Later Analysis¶
In [33]:
df4 = pd.DataFrame({col: str(col)+'=' for col in df3},
index=df3.index) + df3.astype(str)
df4.head()
Out[33]:
Part 3: Visualize Frequent Itemsets (More EDA)¶
In [34]:
# use pd.melt methond to pivot the dataframe
melted_data = pd.melt(df4)
frequency = melted_data.groupby(by=['value'])['value'].count().sort_values(ascending=True)
freq_itemset = pd.DataFrame({'item':frequency.index,'frequency':frequency.values})
3.1 the 10 least frequent itemsets¶
In [35]:
g = sns.barplot(data = freq_itemset.head(10),x='item',y='frequency',color="salmon", saturation=.5)
g.set_xticklabels(g.get_xticklabels(),rotation=90)
plt.show()
3.2 the 10 most frequent itemsets¶
In [36]:
# Visualize the 10 most frequent itemsets
g = sns.barplot(data = freq_itemset.tail(10),x='item',y='frequency',color="salmon", saturation=.5)
g.set_xticklabels(g.get_xticklabels(),rotation=90)
plt.show()
Part 4: Modeling¶
4.1 Data processing for the Apriori library¶
- The Apriori library requires the dataset to be in the form of a list of lists
In [37]:
records = []
for i in range(0,len(df4)):
records.append([str(df4.values[i,j])
for j in range(0, len(df4.columns))])
records[0]
Out[37]:
4.2 Run association rule mining algorithm using default settings as a baseline model¶
In [38]:
te = TransactionEncoder()
te_ary = te.fit(records).transform(records)
# For our convenience, we can turn the encoded array into a pandas DataFrame
df5 = pd.DataFrame(te_ary, columns=te.columns_)
df5.head(3)
Out[38]:
In [39]:
df5.to_csv('df5.csv',header=True, encoding = 'utf-8',index = False)
In [40]:
frequent_itemsets = apriori(df5,use_colnames=True)
frequent_itemsets.sort_values(by='support',ascending=False)
rules = association_rules(frequent_itemsets, metric="lift")
rules.head()
Out[40]:
Plot Association Rules for baseline model¶
In [41]:
b = sns.scatterplot(data=rules,x='support',y='confidence',hue='lift')
plt.show()
4.3 Fine tune the model by experimenting with different algorithm hyper-parameters¶
- Supoprt: (Transactions containing (B))/(Total Transactions)
- Confidence: (Transactions containing both (A and B))/(Transactions containing A)
- Lift: how much more often the antecedent and consequent of a rule A->C occur together than we would expect if they were statistically independent. (Confidence (A→B))/(Support (B))
In [42]:
frequent_itemsets2 = apriori(df5, min_support=0.6, use_colnames=True)
frequent_itemsets2.sort_values(by='support',ascending=False)
rules2 = association_rules(frequent_itemsets2, metric="lift", min_threshold=1)
rules2[(rules['lift']>1) & (rules['confidence'] > 0.5)].head(10)
Out[42]:
Plot Association Rules for new model (A comparision of the base model and the new model)¶
In [43]:
fig=plt.figure(figsize=(12,4)) # crate a new figure
ax0=fig.add_subplot(121)
ax0 = sns.scatterplot(data=rules,x='support',y='confidence',hue='lift')
ax0.set_title("Baseline Model",fontsize=8)
ax1=fig.add_subplot(122)
ax1 = sns.scatterplot(data=rules2,x='support',y='confidence',hue='lift')
ax1.set_title("Parameter Tuned",fontsize=8);
4.3 Discuss how tuning those hyper-parameters could impact the model performance (e.g. overfitting or underfiting).¶
- In the Assoication Rule Minning model, we have three hyper-paremeters. They are Support, Confidence, and Lift. These parameters are used to exclude rules in the result that have a support or a confidence lower than the minimum support, minimum confidence ,and lift respectively.
- Underfitting may occurs if we don't set the values of three hyper-paremeter or set their values at a very low level. Under such situation, our model is going to reutrn us too many results relative to the number of itemsets. It can netither capture the undering pattern of the data nor generalize to new data.
- Overfitting may occurs if we set the values of three hyper-parameter very high or use a unusual combination of values. Under such situation, our model is going to give us too few results relative to the number of itemsets, which means that our model is too closely fit to a limited set of data points. In other words, the returned combinations we see in that speicfic dataset might not be observed in another similar dataset.
Part 5: Classification with Association Rule Mining¶
- Output and present the most interesting and significant rules which could predict “Attrition”
- print out the top 5 rules which predict those who stay vs. who leave, respectively
5.1 Preparation¶
Step1: Looking at the distribution of "Attribution".¶
- The support for "Attrition=Yes" is around 15.73% while the support for "Attrition=No" is around 84.27%.
- This indicates that we should choose a relatively high support value for "Attrition=Yes" and a realtively low support value for "Attrition=No" when doing the classification.
In [44]:
df3['Attrition'].value_counts()
print('Support of Attrition=Yes:', 185/(991+185))
print('Support of Attrition=No:', 991/(991+185))
Step2: Defining a function to do the classification with Association Rule Mining¶
In [45]:
def SupervisedApriori(data,consequent,min_supp,min_conf,min_lift):
frequent_itemsets = apriori(data, min_support=min_supp, use_colnames=True)
rules = association_rules(frequent_itemsets, metric="confidence", min_threshold=min_conf)
rules = rules[rules['lift'] > min_lift]
sup_rules = pd.DataFrame()
for i in consequent:
df = rules[rules['consequents'] == {i}]
sup_rules = sup_rules.append(df,ignore_index = True)
return(sup_rules)
5.2 The top 5 rules which predict those who stay¶
Step1: Coming up with the a list of rules that have "Attrition=No" as consequents¶
In [46]:
att_no = SupervisedApriori(df5,consequent = ['Attrition=No'],min_supp=0.5, min_conf=0.5, min_lift=1)
att_no = att_no.sort_values(by='lift',ascending = False)
att_no.head()
Out[46]:
Step2: removing redundant rules: in the presence of more general rules, the specific rules are considereed as redundant and should be removed.¶
In [47]:
att_no_2 = att_no[:]
alist = []
for i in range(0,20):
for j in range(1,20):
if set(att_no_2['antecedents'][i]).issubset(set(att_no_2['antecedents'][j])) and att_no_2['antecedents'][i]!= att_no_2['antecedents'][j]:
alist.append(i)
alist = set(alist)
alist = list(alist)
print(alist)
att_no_2.drop(df.index[alist],inplace=True)
In [48]:
att_no_2.head(5)
Out[48]:
5.3 The top 5 rules which predict those who leaves¶
Step1: Coming up with the a list of rules that have "Attrition=No" as consequents¶
In [49]:
att_yes = SupervisedApriori(df5,consequent = ['Attrition=Yes'],min_supp=0.1, min_conf=0.0, min_lift=0.0)
att_yes = att_yes.sort_values(by='lift',ascending=False)
att_yes.head()
Out[49]:
Step2: removing redundant rules: in the presence of more general rules, the specific rules are considereed as redundant and should be removed.¶
In [50]:
att_yes_2 = att_yes[:]
alist2 = []
for i in range(0,29):
for j in range(1,29):
if set(att_yes_2['antecedents'][i]).issubset(set(att_yes_2['antecedents'][j])) and att_yes_2['antecedents'][i]!= att_yes_2['antecedents'][j]:
alist2.append(i)
alist2 = set(alist2)
alist2 = list(alist2)
print(alist2)
att_yes_2.drop(df.index[alist2],inplace=True)
In [51]:
att_yes_2.head(5)
Out[51]:
In [52]:
# show the full antecedents
print('Rule 2:', att_yes_2['antecedents'][27],'\n')
print('Rule 3:', att_yes_2['antecedents'][28],'\n')
Part 6: Model Interpretation¶
- Provide interpretations of the above chosen association rules and also discuss why you consider them interesting and significant.
6.1 Discovering interesting and significant rules using 'Lift'¶
- In IST687, I learned that a more interesting rule could be a more useful rule (significant) becuase it is more novel and unexpected.
- Lift is one such measure to tell how interesting and significant a rule is. The lift value of an association rule is the ratio of the confidence of the rule and the expected confidence of the rule. Intuitively speaking, lift measures how much better the rule is at predicting presence of a consequent compared to just relying on the raw probability of the consequent in the dataset.
- Therefore, in part 4, to find out interesting and significant rules, I used 'Lift' to sort the rules at the desending order. After removing the duplicated rules, I selected the top 5 rules for "Attrition=Yes" and "Attrition=No" respeectively.
6.2 Those who stay¶
Rule 1: (PerformanceRating=3.0, OverTime=No) --> (Attrition=No)¶
- This group of employees has relatively good job performance and doesn't work overtime, indicating that these employees have low work stress and a relatively good work-life balance.
- This rule is intesting becuaes sometimes we tend to think that, after being capable of doing current job, people desires a new job that is more challenging than the current one.
Rule 2: (YearsAtCompany_grp=few_yrs_comp, YearsSinceLastPromotion_grp=rec_since_promo, OverTime=No) --> (Attrition=No)¶
- This group of employees has been in their company for a few years and have been promoted recently. They don't work overtime either. Therefore, we can conclude that this group of employees is very capable of doing their job and has a promising future in their current companies.
- This rule is intersting in two ways. First, star workers is very likely to receive invitations from recuriters about new job opportunities and to jump to a new company. Second, some people like to jump to a new company for a big rise if they are well-recognized in their current company.
Rule 3: (DistanceFromHome_grp=walking_dist) --> (Attrition=No)¶
- The group of employees live very close to their companies.
- This rule is interesting beccuase in real life, we often underlook how living close to a company can help imporving quality of life.
Rule 4: (Department=Research & Development) --> (Attrition=No)¶
- This group of employees are working in the Research & Development field.
- This rule is interesting in a way that these well-educated and workers are very popular among companies and could have many great working opportunites. Thus, they have higher loyalty than expected.
Rule 5: (WorkLifeBalance=3) --> (Attrition=No)¶
- This group of employees have acceptable level of worklifebalance.
- This rule is intersting and siginificant becuase it tells us that companies don't have to always provide the best work-life balance for thier employees when they want to decrease attrition rate.
6.3 Those who leaves¶
Rule 1: (StockOptionLevel=0) --> (Attrition=Yes)¶
- This group of employees doesn't have any stock.
- Based on our common sense, employees with no stock option are generally new to companies and would work very hard to try to get promoted or rises in the future.
Rule 2: ('YearsAtCompany_grp=few_yrs_comp', 'YearsSinceLastPromotion_grp=rec_since_promo', 'YearsWithCurrManager_grp=rec_manager') --> (Attrition=Yes)¶
- This group of employees has been in their company for a few years and have been promoted recently. They are recently assigned to their current manager.
- This rule is intersting becuase it tells us that geting a promotion isn't enough to keep an employee staying in the company. Employees take thier manager into account when making decision of leaving.
Rule 3: ('YearsAtCompany_grp=few_yrs_comp', 'TotalWorkingYears_grp=few_wrok_yrs', 'YearsInCurrentRole_grp=few_yrs_role', 'YearsSinceLastPromotion_grp=rec_since_promo'） --> (Attrition=Yes)¶
- This group of employees have limited working expereinces and they seems to be doing their first job. Not long after promotion, they left the company.
- This rule is significant becuase it warns companies that when promoting relatively new employees, companies should take into account employees' willingness to stay in the company.
Rule 4: (YearsAtCompany_grp=few_yrs_comp, YearsInCurrentRole_grp=few_yrs_role, PerformanceRating=3.0) --> (Attrition=Yes)¶
- This group of employees seems to be doing the same role since they come to their companies. Their performance level is okay.
- To effectviely decrease the attrition rate, company should look into how to retain these med-performance.
Rule 5: (YearsAtCompany_grp=few_yrs_comp, TotalWorkingYears_grp=few_wrok_yrs, PerformanceRating=3.0) --> (Attrition=Yes)¶
- This group of employees seems to be having their first jobs in comapnies. They have med-level performance and they left the caompnies.
- This rule is interesting becuase it tells us that in the first couple working years, employees wii decided if they are good at doing their jobs.